package gem.model.dao.mysql;

import gem.model.core.Course;
import gem.model.core.Department;
import gem.model.core.ScienceDomain;
import gem.database.Database;
import gem.model.dao.Common;
import gem.model.dao.DepartmentDAO;

import java.sql.SQLException;
import java.util.Collection;

public class MySqlDepartmentDAO implements DepartmentDAO
{

    private static Department defaultDepartment = null;

    public static Department testGetDefaultDepartmnet() throws SQLException
    {
        return getDefaultDepartment();
    }

    protected static final Department getDefaultDepartment() throws SQLException
    {
        if( defaultDepartment == null )
        {
            MySqlDepartmentDAO dao = new MySqlDepartmentDAO();
            if( ( defaultDepartment = dao.findDepartment("")) == null )
            {
                defaultDepartment = new Department().setId( dao.insertDepartment("")).setTitle("");
            }
        }

        return defaultDepartment;
    }

    public int insertDepartment(String name) throws SQLException
    {
        String query = "INSERT INTO department( departmenttitle ) VALUES ";
        query += "('" + name + "');";
        java.sql.Connection conn = null;
        java.sql.ResultSet rs = null;
        int newId = 0;

        try
        {
            conn = Database.getConnection();
            java.sql.PreparedStatement pStat = conn.prepareStatement(query, java.sql.Statement.RETURN_GENERATED_KEYS);

            int numRows = pStat.executeUpdate();

            if (numRows == 0)
            {
                throw new SQLException("Cannot insert department " + name );
            }

            rs = pStat.getGeneratedKeys();

            if (rs.first())
            {
                newId = rs.getInt(1);                
            }
            else
            {
                throw new SQLException("Cannot insert department " + name );
            }

            /*
             * we will not insert into this table, unless we have some sciencedomain
            query = "INSERT INTO sciencedomaincourses( departmentid, sciencedomainid, courseid ) VALUES " +
            "( " + newId + " , " + sdId + " , " + cId + " ); ";

            conn.createStatement().executeUpdate( query );
             */
        } finally
        {
            if (rs != null)
            {
                rs.close();
            }
            if (conn != null)
            {
                conn.close();
            }
        }

        return newId;
    }

    // The following methods can use
    // MySqlDAOFactory.createConnection()
    // to get a connection as required
    @Override
    public int insertDepartment(Department d) throws SQLException
    {
        // TODO Auto-generated method stub
        // insert into Department, and insert into ScienceDomainCourse
        String query = "INSERT INTO department( departmenttitle ) VALUES ";
        query += "('" + d.getTitle() + "');";
        java.sql.Connection conn = null;
        java.sql.ResultSet rs = null;
        int newId = 0;

        try
        {
            conn = Database.getConnection();
            java.sql.PreparedStatement pStat = conn.prepareStatement(query, java.sql.Statement.RETURN_GENERATED_KEYS);

            int numRows = pStat.executeUpdate();            

            if (numRows == 0)
            {
                throw new SQLException("Cannot insert department " + d);
            }

            rs = pStat.getGeneratedKeys();

            if (rs.first())
            {
                newId = rs.getInt(1);
                d.setId(newId);
            }
            else
            {
                throw new SQLException("Cannot insert department " + d);
            }

            /*
             * we will not insert into this table, unless we have some sciencedomain
            query = "INSERT INTO sciencedomaincourses( departmentid, sciencedomainid, courseid ) VALUES " +
            "( " + newId + " , " + sdId + " , " + cId + " ); ";

            conn.createStatement().executeUpdate( query );
             */
        } finally
        {
            if (rs != null)
            {
                rs.close();
            }
            if (conn != null)
            {
                conn.close();
            }
        }

        return newId;
    }

    @Override
    public boolean deleteDepartment(Department d) throws SQLException
    {
        // TODO Auto-generated method stub
        // list all sciencedomain, and for each perfom scienceDOmainDAO.deleteScienceDomain( s )
        // then delete from department
        // we are forming in = ( 1,2,3,4 )
        String inCourses = "(";
        String inSD = "(";

        java.util.Collection<ScienceDomain> sDomains = d.getScienceDomains();
        boolean foundSD = false;
        boolean foundCourse = false;

        if (sDomains != null && sDomains.size() > 0)
        {
            foundSD = true;
            for (ScienceDomain sd : sDomains)
            {
                inSD += (sd.getId() + " , ");
                java.util.Collection<Course> cs = sd.getCourses();
                if (cs != null && cs.size() > 0)
                {
                    foundCourse = true;
                    for (Course c : cs)
                    {
                        inCourses += (c.getId() + " , ");
                    }
                }
            }

        }
        if (foundCourse)
        {
            inCourses = inCourses.substring(0, inCourses.lastIndexOf(","));
            inCourses += ")";
        }

        if (foundSD)
        {
            inSD = inSD.substring(0, inSD.lastIndexOf(","));
            inSD += ")";
        }

        java.sql.Connection conn = null;

        try
        {
            String query;
            conn = Database.getConnection();

            // if there is some course, we delete them
            if (foundCourse)
            {
                query = "DELETE FROM course WHERE courseid IN " + inCourses + ";";
                conn.createStatement().executeUpdate(query);
            }
            // also, remove sciencedomains only there is some sciencedomain
            if (foundSD)
            {
                query = "DELETE FROM sciencedomain WHERE sciencedomainid IN " + inSD + ";";
                System.out.println(query);
                conn.createStatement().executeUpdate(query);
            }

            query = "DELETE FROM department WHERE departmentid = '" + d.getId() + "';";
            conn.createStatement().executeUpdate(query);

            return true;
        } finally
        {
            if (conn != null)
            {
                conn.close();
            }
        }
    }

    @Override
    public Collection<Department> listDepartments() throws SQLException
    {
        // TODO Auto-generated method stub
        // select * from department
        String query = "SELECT * FROM department";
        java.util.Collection<Department> deps = new java.util.ArrayList();

        java.sql.Connection conn = null;
        java.sql.ResultSet rs = null;
        try
        {
            conn = Database.getConnection();
            rs = conn.createStatement().executeQuery(query);

            while (rs.next())
            {
                deps.add(new Department().setId(rs.getInt(Common.DepartmentId)).setTitle(rs.getString(Common.DepartmentTitle)));
            }
        } finally
        {
            if (rs != null)
            {
                rs.close();
            }
            if (conn != null)
            {
                conn.close();
            }
        }

        return deps;
    }

    @Override
    public boolean updateDepartment(Department d) throws SQLException
    {
        // TODO Auto-generated method stub
        // update DepartmentTable
        String query = "UPDATE department SET departmenttitle = '" + d.getTitle() + "' WHERE departmentid = '"
                + d.getId() + "' ;";

        java.sql.Connection conn = null;
        try
        {
            conn = Database.getConnection();
            conn.createStatement().executeUpdate(query);
        } finally
        {
            if (conn != null)
            {
                conn.close();
            }
        }

        return false;
    }

    @Override
    public boolean moveScienceDomain(ScienceDomain sd, Department from,
            Department to) throws SQLException
    {
        // TODO Auto-generated method stub
        // update ScienceDomainCourse - every occurence of from, replace with to
        String query = "UPDATE sciencedomaincourses SET departmentid = '" + to.getId() + "' WHERE "
                + "sciencedomainid = '" + sd.getId() + "' ; ";
        java.sql.Connection conn = null;
        try
        {
            conn = Database.getConnection();
            conn.createStatement().executeUpdate(query);
        }
        finally
        {
            if (conn != null)
            {
                conn.close();
            }
        }

        return true;
    }

    @Override
    public Department findDepartment(String s) throws SQLException
    {
        String query = "SELECT * FROM department WHERE departmenttitle ='" + s + "';";
        java.sql.Connection conn = null;
        java.sql.ResultSet rs = null;
        Department dep = null;
        try
        {
            conn = Database.getConnection();
            rs = conn.createStatement().executeQuery(query);
            if (rs.first())
            {
                dep = new Department().setId(rs.getInt(Common.DepartmentId)).setTitle(rs.getString(Common.DepartmentTitle));
            }
        } finally
        {
            if (rs != null)
            {
                rs.close();
            }
            if (conn != null)
            {
                conn.close();
            }
        }

        return dep;
    }

    @Override
    public Collection<ScienceDomain> listScienceDomains(Department d) throws SQLException
    {
        String query = "SELECT DISTINCT sd.sciencedomainid AS id, sd.sciencedomaintitle AS title FROM "
                + " sciencedomain sd, sciencedomaincourses sdc WHERE "
                + " sd.sciencedomainid = sdc.sciencedomainid AND "
                + "sdc.departmentid = '" + d.getId() + "' ; ";

        java.sql.Connection conn = null;
        java.sql.ResultSet rs = null;
        java.util.Collection<ScienceDomain> sds = new java.util.Vector<ScienceDomain>();
        try
        {
            conn = Database.getConnection();
            rs = conn.createStatement().executeQuery(query);

            while (rs.next())
            {
                sds.add(new ScienceDomain().setId(rs.getInt("id")).setTitle(rs.getString("title")));
            }
        } finally
        {
            if (rs != null)
            {
                rs.close();
            }
            if (conn != null)
            {
                conn.close();
            }
        }
        return sds;
    }

}
